﻿/***********************************************************************************************************************************
 *    Name: Partition_YYYYMM.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Set up partitioning for monthly groups.
**/

DECLARE
  @name	NVARCHAR(128)= DB_NAME(),
  @path	NVARCHAR(255)= master.DBAdmin.GetInstancePath('DefaultData')
PRINT @path;

WITH
	yyyy (year) AS (SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012 UNION ALL SELECT 2013 UNION ALL SELECT 2014 UNION ALL SELECT 2015 UNION ALL SELECT 2016 UNION ALL SELECT 2017 UNION ALL SELECT 2018 UNION ALL SELECT 2019),
	mm  (month) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12),
    fg (yyyymm) AS (SELECT CAST(year AS CHAR(4)) + RIGHT('00'+CAST(month AS VARCHAR(2)),2) FROM yyyy CROSS JOIN mm)
SELECT sql= N'ALTER DATABASE [' + @name + N'] ADD FILEGROUP [fg' + yyyymm + N']; ALTER DATABASE [' + @name + N'] ADD FILE (NAME= ''' + @name + N'_' + yyyymm + N''', FILENAME= ''' + @path + @name + N'_' + yyyymm + N'.ndf'', SIZE= 512kb, FILEGROWTH= 64MB) TO FILEGROUP [fg' + yyyymm + N'];'
  FROM fg


WITH
	yyyy (year) AS (SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012 UNION ALL SELECT 2013 UNION ALL SELECT 2014 UNION ALL SELECT 2015 UNION ALL SELECT 2016 UNION ALL SELECT 2017 UNION ALL SELECT 2018 UNION ALL SELECT 2019),
	mm  (month) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12),
    fg (yyyymm) AS (SELECT CAST(year AS CHAR(4)) + RIGHT('00'+CAST(month AS VARCHAR(2)),2) FROM yyyy CROSS JOIN mm)
  SELECT sql= N'CREATE PARTITION FUNCTION pf_YYYYMM (NVARCHAR(6)) AS RANGE FOR VALUES ('
GO

CREATE PARTITION FUNCTION pf_YYYYMM00 (NCHAR(8)) AS RANGE FOR VALUES (
	N'20110100',N'20110200',N'20110300',N'20110400',N'20110500',N'20110600',N'20110700',N'20110800',N'20110900',N'20111000',N'20111100',N'20111200',
	N'20120100',N'20120200',N'20120300',N'20120400',N'20120500',N'20120600',N'20120700',N'20120800',N'20120900',N'20121000',N'20121100',N'20121200',
	N'20130100',N'20130200',N'20130300',N'20130400',N'20130500',N'20130600',N'20130700',N'20130800',N'20130900',N'20131000',N'20131100',N'20131200',
	N'20140100',N'20140200',N'20140300',N'20140400',N'20140500',N'20140600',N'20140700',N'20140800',N'20140900',N'20141000',N'20141100',N'20141200',
	N'20150100',N'20150200',N'20150300',N'20150400',N'20150500',N'20150600',N'20150700',N'20150800',N'20150900',N'20151000',N'20151100',N'20151200',
	N'20160100',N'20160200',N'20160300',N'20160400',N'20160500',N'20160600',N'20160700',N'20160800',N'20160900',N'20161000',N'20161100',N'20161200',
	N'20170100',N'20170200',N'20170300',N'20170400',N'20170500',N'20170600',N'20170700',N'20170800',N'20170900',N'20171000',N'20171100',N'20171200',
	N'20180100',N'20180200',N'20180300',N'20180400',N'20180500',N'20180600',N'20180700',N'20180800',N'20180900',N'20181000',N'20181100',N'20181200',
	N'20190100',N'20190200',N'20190300',N'20190400',N'20190500',N'20190600',N'20190700',N'20190800',N'20190900',N'20191000',N'20191100',N'20191200');
CREATE PARTITION SCHEME ps_YYYYMM00 AS PARTITION pf_YYYYMM00 TO (
	[fg201101],[fg201102],[fg201103],[fg201104],[fg201105],[fg201106],[fg201107],[fg201108],[fg201109],[fg201110],[fg201111],[fg201112],
	[fg201201],[fg201202],[fg201203],[fg201204],[fg201205],[fg201206],[fg201207],[fg201208],[fg201209],[fg201210],[fg201211],[fg201212],
	[fg201301],[fg201302],[fg201303],[fg201304],[fg201305],[fg201306],[fg201307],[fg201308],[fg201309],[fg201310],[fg201311],[fg201312],
	[fg201401],[fg201402],[fg201403],[fg201404],[fg201405],[fg201406],[fg201407],[fg201408],[fg201409],[fg201410],[fg201411],[fg201412],
	[fg201501],[fg201502],[fg201503],[fg201504],[fg201505],[fg201506],[fg201507],[fg201508],[fg201509],[fg201510],[fg201511],[fg201512],
	[fg201601],[fg201602],[fg201603],[fg201604],[fg201605],[fg201606],[fg201607],[fg201608],[fg201609],[fg201610],[fg201611],[fg201612],
	[fg201701],[fg201702],[fg201703],[fg201704],[fg201705],[fg201706],[fg201707],[fg201708],[fg201709],[fg201710],[fg201711],[fg201712],
	[fg201801],[fg201802],[fg201803],[fg201804],[fg201805],[fg201806],[fg201807],[fg201808],[fg201809],[fg201810],[fg201811],[fg201812],
	[fg201901],[fg201902],[fg201903],[fg201904],[fg201905],[fg201906],[fg201907],[fg201908],[fg201909],[fg201910],[fg201911],[fg201912],[fg202000]);

CREATE PARTITION FUNCTION pf_YYYYMM (NCHAR(6)) AS RANGE FOR VALUES (
	N'201101',N'201102',N'201103',N'201104',N'201105',N'201106',N'201107',N'201108',N'201109',N'201110',N'201111',N'201112',
	N'201201',N'201202',N'201203',N'201204',N'201205',N'201206',N'201207',N'201208',N'201209',N'201210',N'201211',N'201212',
	N'201301',N'201302',N'201303',N'201304',N'201305',N'201306',N'201307',N'201308',N'201309',N'201310',N'201311',N'201312',
	N'201401',N'201402',N'201403',N'201404',N'201405',N'201406',N'201407',N'201408',N'201409',N'201410',N'201411',N'201412',
	N'201501',N'201502',N'201503',N'201504',N'201505',N'201506',N'201507',N'201508',N'201509',N'201510',N'201511',N'201512',
	N'201601',N'201602',N'201603',N'201604',N'201605',N'201606',N'201607',N'201608',N'201609',N'201610',N'201611',N'201612',
	N'201701',N'201702',N'201703',N'201704',N'201705',N'201706',N'201707',N'201708',N'201709',N'201710',N'201711',N'201712',
	N'201801',N'201802',N'201803',N'201804',N'201805',N'201806',N'201807',N'201808',N'201809',N'201810',N'201811',N'201812',
	N'201901',N'201902',N'201903',N'201904',N'201905',N'201906',N'201907',N'201908',N'201909',N'201910',N'201911',N'201912');
CREATE PARTITION SCHEME ps_YYYYMM AS PARTITION pf_YYYYMM TO (
	[fg201101],[fg201102],[fg201103],[fg201104],[fg201105],[fg201106],[fg201107],[fg201108],[fg201109],[fg201110],[fg201111],[fg201112],
	[fg201201],[fg201202],[fg201203],[fg201204],[fg201205],[fg201206],[fg201207],[fg201208],[fg201209],[fg201210],[fg201211],[fg201212],
	[fg201301],[fg201302],[fg201303],[fg201304],[fg201305],[fg201306],[fg201307],[fg201308],[fg201309],[fg201310],[fg201311],[fg201312],
	[fg201401],[fg201402],[fg201403],[fg201404],[fg201405],[fg201406],[fg201407],[fg201408],[fg201409],[fg201410],[fg201411],[fg201412],
	[fg201501],[fg201502],[fg201503],[fg201504],[fg201505],[fg201506],[fg201507],[fg201508],[fg201509],[fg201510],[fg201511],[fg201512],
	[fg201601],[fg201602],[fg201603],[fg201604],[fg201605],[fg201606],[fg201607],[fg201608],[fg201609],[fg201610],[fg201611],[fg201612],
	[fg201701],[fg201702],[fg201703],[fg201704],[fg201705],[fg201706],[fg201707],[fg201708],[fg201709],[fg201710],[fg201711],[fg201712],
	[fg201801],[fg201802],[fg201803],[fg201804],[fg201805],[fg201806],[fg201807],[fg201808],[fg201809],[fg201810],[fg201811],[fg201812],
	[fg201901],[fg201902],[fg201903],[fg201904],[fg201905],[fg201906],[fg201907],[fg201908],[fg201909],[fg201910],[fg201911],[fg201912],[fg202000]);
GO